package com.enterpriseDigitalPlatform.controller.renshi.staff;

import com.enterpriseDigitalPlatform.dto.PageRes;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class StaffDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public StaffDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // 新加员工信息
    public int addData(StaffDTO staffDTO) {
        ArrayList<String> keyArr = new ArrayList<String>();
        ArrayList<String> placeArr = new ArrayList<String>();
        ArrayList<Object> valueArr = new ArrayList<Object>();
        ArrayList<String> updateArr = new ArrayList<>();
        keyArr.add("cardid");
        placeArr.add("?");
        valueArr.add(staffDTO.getCardid());
        if (staffDTO.getName() != null) {
            keyArr.add("name");
            placeArr.add("?");
            valueArr.add(staffDTO.getName());
            updateArr.add("name=values(name)");
        }
        if (staffDTO.getTel() != null) {
            keyArr.add("tel");
            placeArr.add("?");
            valueArr.add(staffDTO.getTel());
            updateArr.add("tel=values(tel)");
        }
        if (staffDTO.getAccount() != null) {
            keyArr.add("account");
            placeArr.add("?");
            valueArr.add(staffDTO.getAccount());
        }
        if (staffDTO.getPwd() != null) {
            keyArr.add("pwd");
            placeArr.add("?");
            valueArr.add(staffDTO.getPwd());
        }
        if (staffDTO.getBirthday() != null) {
            keyArr.add("birthday");
            placeArr.add("?");
            valueArr.add(staffDTO.getBirthday());
            updateArr.add("birthday=values(birthday)");
        }
        if (staffDTO.getBirthMonth() != null) {
            keyArr.add("birth_month");
            placeArr.add("?");
            valueArr.add(staffDTO.getBirthMonth());
            updateArr.add("birth_month=values(birth_month)");
        }
        if (staffDTO.getBirthYear() != null) {
            keyArr.add("birth_year");
            placeArr.add("?");
            valueArr.add(staffDTO.getBirthYear());
            updateArr.add("birth_year=values(birth_year)");
        }
        if (staffDTO.getSex() != null) {
            keyArr.add("sex");
            placeArr.add("?");
            valueArr.add(staffDTO.getSex());
            updateArr.add("sex=values(sex)");
        }
        if (staffDTO.getAddr() != null) {
            keyArr.add("addr");
            placeArr.add("?");
            valueArr.add(staffDTO.getAddr());
            updateArr.add("addr=values(addr)");
        }
        if (staffDTO.getStatus() != null) {
            keyArr.add("status");
            placeArr.add("?");
            valueArr.add(staffDTO.getStatus());
        }
        if (staffDTO.getIsBlack() != null) {
            keyArr.add("is_black");
            placeArr.add("?");
            valueArr.add(staffDTO.getIsBlack());
        }
        if (staffDTO.getDeptid() != null) {
            keyArr.add("deptid");
            placeArr.add("?");
            valueArr.add(staffDTO.getDeptid());
            updateArr.add("deptid=values(deptid)");
        }
        if (staffDTO.getIntoOutId() != null) {
            keyArr.add("into_out_id");
            placeArr.add("?");
            valueArr.add(staffDTO.getIntoOutId());
            updateArr.add("into_out_id=values(into_out_id)");
        }
        if (staffDTO.getPosition() != null) {
            keyArr.add("position");
            placeArr.add("?");
            valueArr.add(staffDTO.getPosition());
            updateArr.add("position=values(position)");
        }
        if (staffDTO.getJobid() != null) {
            keyArr.add("jobid");
            placeArr.add("?");
            valueArr.add(staffDTO.getJobid());
            updateArr.add("jobid=values(jobid)");
        }
        if (staffDTO.getRoleid() != null) {
            keyArr.add("roleid");
            placeArr.add("?");
            valueArr.add(staffDTO.getRoleid());
            updateArr.add("roleid=values(roleid)");
        }
        String sql = "insert into renshi_staff(" + String.join(",", keyArr) + ") values(" + String.join(",", placeArr) + ")";
        if (!updateArr.isEmpty()) {
            sql += " on duplicate key update " + String.join(",", updateArr);
        }
        return jdbcTemplate.update(sql, valueArr.toArray());
    }

    // 编辑员工信息
    public int editData(StaffDTO staffDTO) {
        ArrayList<String> keyArr = new ArrayList<String>();
        ArrayList<Object> valueArr = new ArrayList<Object>();
        if (staffDTO.getName() != null) {
            keyArr.add("name = ?");
            valueArr.add(staffDTO.getName());
        }
        if (staffDTO.getTel() != null) {
            keyArr.add("tel = ?");
            valueArr.add(staffDTO.getTel());
        }
        if (staffDTO.getAccount() != null) {
            keyArr.add("account = ?");
            valueArr.add(staffDTO.getAccount());
        }
        if (staffDTO.getBirthday() != null) {
            keyArr.add("birthday = ?");
            valueArr.add(staffDTO.getBirthday());
        }
        if (staffDTO.getBirthMonth() != null) {
            keyArr.add("birth_month = ?");
            valueArr.add(staffDTO.getBirthMonth());
        }
        if (staffDTO.getBirthYear() != null) {
            keyArr.add("birth_year = ?");
            valueArr.add(staffDTO.getBirthYear());
        }
        if (staffDTO.getSex() != null) {
            keyArr.add("sex = ?");
            valueArr.add(staffDTO.getSex());
        }
        if (staffDTO.getAddr() != null) {
            keyArr.add("addr = ?");
            valueArr.add(staffDTO.getAddr());
        }
        if (staffDTO.getStatus() != null) {
            keyArr.add("status = ?");
            valueArr.add(staffDTO.getStatus());
        }
        if (staffDTO.getDeptid() != null) {
            keyArr.add("deptid = ?");
            valueArr.add(staffDTO.getDeptid());
        }
        if (staffDTO.getIntoOutId() != null) {
            keyArr.add("into_out_id = ?");
            valueArr.add(staffDTO.getIntoOutId());
        }
        if (staffDTO.getPosition() != null) {
            keyArr.add("position = ?");
            valueArr.add(staffDTO.getPosition());
        }
        if (staffDTO.getJobid() != null) {
            keyArr.add("jobid = ?");
            valueArr.add(staffDTO.getJobid());
        }
        if (staffDTO.getRoleid() != null) {
            keyArr.add("roleid = ?");
            valueArr.add(staffDTO.getRoleid());
        }
        if (!valueArr.isEmpty()) {
            String sql = "update renshi_staff set " + String.join(",", keyArr) + " where id = ? and is_black = 0";
            valueArr.add(staffDTO.getId());
            return jdbcTemplate.update(sql, valueArr.toArray());
        }
        return 0;
    }

    // 编辑员工信息 黑名单
    public int editBlack(StaffDTO staffDTO) {
        String sql = "update renshi_staff set is_black = ? where id = ?";
        ArrayList<Object> valueArr = new ArrayList<Object>();
        valueArr.add(staffDTO.getIsBlack());
        valueArr.add(staffDTO.getId());
        return jdbcTemplate.update(sql, valueArr.toArray());
    }

    // 重置修改密码
    public int editPwd(StaffDTO staffDTO) {
        String sql = "update renshi_staff set pwd = ? where id = ? and is_black = 0";
        ArrayList<Object> valueArr = new ArrayList<Object>();
        valueArr.add(staffDTO.getPwd());
        valueArr.add(staffDTO.getId());
        return jdbcTemplate.update(sql, valueArr.toArray());
    }

    // 获取员工信息
    public PageRes<Staff> getList(StaffDTO staffDTO) {
        ArrayList<String> keyArr = new ArrayList<String>();
        ArrayList<Object> valueArr = new ArrayList<Object>();
        if (staffDTO.getId() != null) {
            keyArr.add("id = ?");
            valueArr.add(staffDTO.getId());
        }
        if (staffDTO.getCardid() != null) {
            keyArr.add("cardid = ?");
            valueArr.add(staffDTO.getCardid());
        }
        if (staffDTO.getName() != null) {
            keyArr.add("name like ?");
            valueArr.add("%" + staffDTO.getName() + "%");
        }
        if (staffDTO.getTel() != null) {
            keyArr.add("tel like ?");
            valueArr.add("%" + staffDTO.getTel() + "%");
        }
        if (staffDTO.getAccount() != null) {
            keyArr.add("account = ?");
            valueArr.add(staffDTO.getAccount());
        }
        if (staffDTO.getBirthdayStart() != null && staffDTO.getBirthdayEnd() != null) {
            keyArr.add("birthday between ? and ?");
            valueArr.add(staffDTO.getBirthdayStart());
            valueArr.add(staffDTO.getBirthdayEnd());
        }
        if (staffDTO.getBirthMonthStart() != null && staffDTO.getBirthMonthEnd() != null) {
            keyArr.add("birth_month between ? and ?");
            valueArr.add(staffDTO.getBirthMonthStart());
            valueArr.add(staffDTO.getBirthMonthEnd());
        }
        if (staffDTO.getBirthYearStart() != null && staffDTO.getBirthYearEnd() != null) {
            keyArr.add("birth_year between ? and ?");
            valueArr.add(staffDTO.getBirthYearStart());
            valueArr.add(staffDTO.getBirthYearEnd());
        }
        if (staffDTO.getSex() != null) {
            keyArr.add("sex = ?");
            valueArr.add(staffDTO.getSex());
        }
        if (staffDTO.getAddr() != null) {
            keyArr.add("addr like ?");
            valueArr.add("%" + staffDTO.getAddr() + "%");
        }
        if (staffDTO.getStatus() != null) {
            keyArr.add("status = ?");
            valueArr.add(staffDTO.getStatus());
        }
        if (staffDTO.getDeptid() != null) {
            keyArr.add("deptid = ?");
            valueArr.add(staffDTO.getDeptid());
        }
        if (staffDTO.getIntoOutId() != null) {
            keyArr.add("into_out_id = ?");
            valueArr.add(staffDTO.getIntoOutId());
        }
        if (staffDTO.getPosition() != null) {
            keyArr.add("position = ?");
            valueArr.add(staffDTO.getPosition());
        }
        if (staffDTO.getJobid() != null) {
            keyArr.add("jobid = ?");
            valueArr.add(staffDTO.getJobid());
        }
        if (staffDTO.getRoleid() != null) {
            keyArr.add("roleid = ?");
            valueArr.add(staffDTO.getRoleid());
        }
        if (staffDTO.getIsBlack() != null) {
            keyArr.add("is_black = ?");
            valueArr.add(staffDTO.getIsBlack());
        }
        String sql = "select * from renshi_staff";
        String sqlCount = "select count(*) from renshi_staff";
        if (!keyArr.isEmpty()) {
            sql += " where " + String.join(" and ", keyArr);
            sqlCount += " where " + String.join(" and ", keyArr);
        }
        Long total = jdbcTemplate.queryForObject(sqlCount, Long.class, valueArr.toArray());
        if (staffDTO.getPage() != null && staffDTO.getPageSize() != null) {
            sql += " order by id desc limit ?,?";
            valueArr.add((staffDTO.getPage() - 1) * staffDTO.getPageSize());
            valueArr.add(staffDTO.getPageSize());
        }
        List<Staff> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Staff.class), valueArr.toArray());
        return new PageRes<>(staffDTO.getPage(), staffDTO.getPageSize(), total, list);
    }
}
